import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.colors import LinearSegmentedColormap
import matplotlib.patches as patches
import numpy as np
import os

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

# === 你提供的准确文件路径 ===
DATA_FILE = r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\data\erp_order_data.xlsx"
RESULTS_DIR = r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\results"
os.makedirs(RESULTS_DIR, exist_ok=True)

def create_annotated_barchart():
    # 1. 读取你指定路径的数据
    print(f"正在从以下路径读取数据:\n{DATA_FILE}")
    df = pd.read_excel(DATA_FILE)
    print(f"✅ 成功加载 {len(df)} 条订单记录")

    # 2. 按商品名称汇总销售金额 (这里用 product_name 代替 "商品")
    sales_by_product = df.groupby('product_name')['product_amount'].sum().sort_values(ascending=False).head(8) # 取前8个商品
    products = sales_by_product.index.tolist()
    sales = sales_by_product.values.tolist()

    # 3. 动态生成标题
    top_product = products[0]
    top_sales = sales[0]
    bottom_product = products[-1]
    bottom_sales = sales[-1]
    title_main = "2021年商品销量情况"
    title_sub = f"{top_product}销量最好达{int(top_sales)}，是{bottom_product}最低值{int(bottom_sales)}近{top_sales/bottom_sales:.1f}倍"

    # 4. 创建图表（保持靛蓝背景风格）
    fig = plt.figure(figsize=(14, 9), facecolor='#1A1A2E')
    ax = fig.add_subplot(111, facecolor='#1A1A2E')

    # 定义多种颜色
    colors = ['#56B4E9', '#E69F00', '#009E73', '#F0E442', '#0072B2', '#D55E00', '#CC79A7', '#888888']

    bar_width = 0.6
    x_pos = np.arange(len(products))

    # 绘制彩色柱子
    bars = ax.bar(x_pos, sales, width=bar_width, color=colors[:len(products)], edgecolor='#E0E0E0', linewidth=2, alpha=0.8)

    # 5. 添加顶部数据标签（带箭头的气泡框）
    for i, v in enumerate(sales):
        # 使用 annotate 来创建带箭头的注释
        ax.annotate(f'{int(v)}',
                    xy=(i, v),
                    xytext=(0, 10),  # 在柱子顶部上方10个单位处
                    textcoords="offset points",
                    ha='center', va='bottom',
                    fontsize=12, fontweight='bold', color='#FFFFFF',
                    bbox=dict(boxstyle='round,pad=0.3', facecolor=colors[i], edgecolor='none', alpha=0.7),
                    arrowprops=dict(arrowstyle='->', color=colors[i], lw=2))

    # 6. 设置样式
    ax.set_title(f"{title_main}\n{title_sub}", fontsize=20, fontweight='bold', pad=25, color='#FFFFFF')
    ax.set_ylabel('销量', fontsize=18, fontweight='bold', color='#E0E0E0')
    ax.set_xlabel('商品', fontsize=16, fontweight='bold', color='#E0E0E0')
    ax.set_xticks(x_pos)
    ax.set_xticklabels(products, fontsize=14, fontweight='bold', color='#E0E0E0')
    ax.tick_params(axis='y', labelsize=14, colors='#E0E0E0')

    for spine in ax.spines.values():
        spine.set_color('#4A4A6A')
        spine.set_linewidth(2)

    ax.grid(axis='y', alpha=0.3, linestyle='--', color='#4A4A6A')
    ax.set_axisbelow(True)
    ax.set_ylim(0, max(sales) * 1.2)

    # 7. 数据来源
    latest_date = pd.to_datetime(df['order_time']).max().strftime('%Y-%m-%d')
    ax.text(0.02, 0.98, f'*注：数据来源于公司销售系统，统计日期截至{latest_date}',
            transform=ax.transAxes, fontsize=10, color='#B0B0B0', alpha=0.7, va='top')

    plt.tight_layout()

    # 8. 保存图表
    output_path = os.path.join(RESULTS_DIR, '04_标注柱形图.png')
    plt.savefig(output_path, dpi=300, bbox_inches='tight', facecolor='#1A1A2E', edgecolor='none')
    plt.show()

    print(f"✅ 第四张图已成功保存至:\n{output_path}")

if __name__ == "__main__":
    create_annotated_barchart()